use crate::role::Role;
use sqlx::{sqlite::SqlitePool, Row, Error};
use std::sync::Arc;

// 数据库连接池类型
type Db = Arc<SqlitePool>;

pub struct RoleDAO {
    db: Db,
}

impl RoleDAO {
    pub fn new(db: Db) -> Self {
        Self { db }
    }

    // 获取所有角色 - 使用预编译SQL
    pub async fn list_roles(&self) -> Result<Vec<Role>, Error> {
        let sql = "SELECT id, name, description FROM roles";
        let rows = sqlx::query(sql)
            .fetch_all(&*self.db)
            .await?;

        let roles = rows
            .into_iter()
            .map(|row| Role {
                id: Some(row.get("id")),
                name: row.get("name"),
                description: row.get("description"),
            })
            .collect();

        Ok(roles)
    }

    // 创建新角色 - 使用预编译SQL和位置参数
    pub async fn create_role(&self, role: &Role) -> Result<i32, Error> {
        let sql = "INSERT INTO roles (name, description) VALUES (?, ?) RETURNING id";
        let row = sqlx::query(sql)
            .bind(&role.name)
            .bind(&role.description)
            .fetch_one(&*self.db)
            .await?;

        let id: i32 = row.get("id");
        Ok(id)
    }

    // 根据ID获取角色 - 使用预编译SQL和位置参数
    pub async fn get_role(&self, id: i32) -> Result<Option<Role>, Error> {
        let sql = "SELECT id, name, description FROM roles WHERE id = ?";
        let row = sqlx::query(sql)
            .bind(id)
            .fetch_optional(&*self.db)
            .await?;

        match row {
            Some(row) => {
                let role = Role {
                    id: Some(row.get("id")),
                    name: row.get("name"),
                    description: row.get("description"),
                };
                Ok(Some(role))
            }
            None => Ok(None),
        }
    }

    // 更新角色 - 使用预编译SQL和命名参数
    pub async fn update_role(&self, id: i32, role: &Role) -> Result<bool, Error> {
        let sql = "UPDATE roles SET name = $1, description = $2 WHERE id = $3";
        let result = sqlx::query(sql)
            .bind(&role.name)
            .bind(&role.description)
            .bind(id)
            .execute(&*self.db)
            .await?;

        Ok(result.rows_affected() > 0)
    }

    // 删除角色 - 使用预编译SQL和位置参数
    pub async fn delete_role(&self, id: i32) -> Result<bool, Error> {
        let sql = "DELETE FROM roles WHERE id = ?";
        let result = sqlx::query(sql)
            .bind(id)
            .execute(&*self.db)
            .await?;

        Ok(result.rows_affected() > 0)
    }
}